const sqlite = require('sqlite');
const sqlite3 = require('sqlite3').verbose();
const databaseConfig = require('../config/database');

let db = null;

// 初始化数据库
async function initDatabase() {
  db = await sqlite.open({
    filename: databaseConfig.path,
    driver: sqlite3.Database
  });

  // 创建表
  await createTables();
  console.log('Database initialized successfully');
}

// 获取数据库连接
async function getDb() {
  if (!db) {
    await initDatabase();
  }
  return db;
}

// 创建所需的表
async function createTables() {
  // 用户表
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      nickname TEXT,
      avatar TEXT,
      type INTEGER,
      organization_id INTEGER,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
  `);

  // 新闻表
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS news (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      abstract_real TEXT,
      class_real INTEGER,
      news_title TEXT NOT NULL,
      news_source TEXT,
      news_time TEXT,
      cover_image TEXT,
      is_select INTEGER DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
  `);
  // 兼容旧库：增加pub_time与is_published列（如已存在则忽略）
  try { await db?.exec('ALTER TABLE news ADD COLUMN pub_time TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN is_published INTEGER DEFAULT 0'); } catch (e) {}
  // 兼容旧库：增加操作人列（如已存在则忽略）
  try { await db?.exec('ALTER TABLE news ADD COLUMN op_person TEXT'); } catch (e) {}
  // 兼容旧库：补齐业务列（如已存在则忽略）
  try { await db?.exec('ALTER TABLE news ADD COLUMN title TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN class_model INTEGER'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN add_mode INTEGER'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN site_name TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN create_time TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN mark_content TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE news ADD COLUMN source_url TEXT'); } catch (e) {}
  // 发布新闻表（恢复原始结构）
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS publish_news (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      news_id INTEGER,
      news_title TEXT NOT NULL,
      class_real INTEGER,
      class_model INTEGER,
      abstract_real TEXT,
      news_source TEXT,
      news_time TEXT,
      create_time TEXT,
      publish_time TEXT,
      wait_pub_time TEXT,
      op_person TEXT,
      pub_state INTEGER DEFAULT 0,
      cover_image TEXT,
      FOREIGN KEY (news_id) REFERENCES news (id) ON DELETE CASCADE
    );
  `);

  // 尝试为发布新闻表添加批次号列（如已存在则忽略）
  try {
    await db?.exec('ALTER TABLE publish_news ADD COLUMN issue_no INTEGER');
  } catch (e) {
    // ignore if column already exists
  }
  // 发布批次表（记录当期发布新闻ID列表等）
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS publish_issue (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      issue_no INTEGER NOT NULL UNIQUE,
      start_date TEXT,
      end_date TEXT,
      news_ids TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
  `);
  // 扩展发布批次表以支持期刊元数据
  try { await db?.exec('ALTER TABLE publish_issue ADD COLUMN title TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE publish_issue ADD COLUMN cover_image TEXT'); } catch (e) {}
  try { await db?.exec('ALTER TABLE publish_issue ADD COLUMN description TEXT'); } catch (e) {}
  try { await db?.exec("ALTER TABLE publish_issue ADD COLUMN publish_time TEXT"); } catch (e) {}
  try { await db?.exec('ALTER TABLE publish_issue ADD COLUMN publisher TEXT'); } catch (e) {}
  // 选中列表
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS release_list (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      news_id INTEGER,
      abstract_real TEXT,
      class_real INTEGER,
      title TEXT NOT NULL,
      site_name TEXT,
      create_time TEXT,
      op_person TEXT,
      is_select INTEGER DEFAULT 1,
      order_news INTEGER DEFAULT 0,
      FOREIGN KEY (news_id) REFERENCES news (id) ON DELETE CASCADE
    );
  `);
  // 索引优化（如已存在则忽略）
  try { await db?.exec('CREATE INDEX IF NOT EXISTS idx_news_is_select ON news(is_select)'); } catch (e) {}
  try { await db?.exec('CREATE INDEX IF NOT EXISTS idx_release_news_id ON release_list(news_id)'); } catch (e) {}
  try { await db?.exec('CREATE INDEX IF NOT EXISTS idx_release_order_news ON release_list(order_news)'); } catch (e) {}

  // 国际化数据表
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS i18n_data (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      code TEXT NOT NULL,
      language_tag TEXT NOT NULL,
      message TEXT NOT NULL,
      remarks TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE (code, language_tag)
    );
  `);

  // 文件上传表
  await db?.exec(`
    CREATE TABLE IF NOT EXISTS files (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      filename TEXT NOT NULL,
      filepath TEXT NOT NULL,
      mimetype TEXT,
      size INTEGER,
      uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
  `);

  // 插入示例用户数据
  await db?.run(
    'INSERT OR IGNORE INTO users (username, password, nickname, type, organization_id) VALUES (?, ?, ?, ?, ?)',
    ['admin', '$2b$10$9eUc55eXvXvZ1K3j5k9e7e5e5e5e5e5e5e5e5e5e5e5e5e5e5', '管理员', 1, 1]
  );

  // 插入示例新闻数据
  await db?.run(
    'INSERT OR IGNORE INTO news (id, abstract_real, class_real, news_title, news_source, news_time, cover_image, is_select) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
    [1, '国家能源局发布2023年光伏产业发展报告', 1, '国家能源局发布2023年光伏产业发展报告', '国家能源局', '2023-12-15 09:30:00', 'https://placehold.net/default.svg', 0]
  );
}

// 关闭数据库连接
async function closeDatabase() {
  if (db) {
    await db.close();
    db = null;
  }
}

module.exports = {
  initDatabase,
  getDb,
  closeDatabase
};